﻿using System;
using System.Data.SqlClient;
using System.Data;
using System.Collections;
using System.Collections.Generic;

/*********************************************
 * Handle database retirval and update
 * 
 * Methods:
 * 
 * 
 * 
 * @Author: 
 * @Date: 
 * ********************************************/

namespace HypermarketSerialPort._1_Model
{
    class DBManager
    {
        private static DBManager DBManagerInstance;
        private ShopDBConnectionManager ShopDBConn = ShopDBConnectionManager.getInstance();
        private DataSet ShopDsTableList = new DataSet();
        private SqlConnection ShopSQLConn = new SqlConnection();

        /************************** Shop Adapter **************************/
        private SqlDataAdapter ShopStaffAdapter;
        private SqlDataAdapter StockDiscountAdapter;
        private SqlDataAdapter ShopStockAdapter;

        private SqlDataAdapter TransactionAdapter;
        private SqlDataAdapter CheckOutItemAdapter;
        private SqlDataAdapter StockAdapter;
        private SqlDataAdapter CashierRegisterAdapter;
        private SqlDataAdapter PriceTagAdapter;
        /************************** Shop CommandBuilder **************************/
        //private SqlCommandBuilder ShopStaffCommandBuilder;
        private SqlCommandBuilder ShopStockCommandBuilder;
        //private SqlCommandBuilder ShopStockDiscountCommandBuilder;
        //private SqlCommandBuilder ShopDiscountCommandBuilder;
        private SqlCommandBuilder ShopCheckOutItemCommandBuilder;
        private SqlCommandBuilder ShopTransactionCommandBuilder;
        private SqlCommandBuilder CashierRegisterCommandBuilder;
        private SqlCommandBuilder PriceTagCommandBuililder;

        Dictionary<string, string> mPriceTagInfo = new Dictionary<string, string>();

        #region Get new DBManager Instance 
        public static DBManager getInstance()
        {
            if (DBManagerInstance == null)
            {
                DBManagerInstance = new DBManager();
            }
            return DBManagerInstance;
        }
        #endregion


        private DBManager()
        {
            ShopSQLConn.ConnectionString = ShopDBConn.getConnectionString();

            try
            {
                ShopDBInitialization();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                CheckAndCloseShopSQLConnection();
            }
        }


        #region ShopDB Initialization

        private void ShopDBInitialization()
        {
            ShopSQLConn.Open();
            InitializeShopSelectCommander();
            InitializeShopAdapterCommands();
            FillShopDataSet();
            ShopSQLConn.Close();
        }

        private void InitializeShopSelectCommander()
        {
            ShopStaffAdapter = new SqlDataAdapter(Constant.CASHIER_TABLE_SELECT_COMMAND, ShopSQLConn);
            StockDiscountAdapter = new SqlDataAdapter(Constant.STOCK_PRODUCT_DISCOUNT_TABLE_SELECT_COMMAND, ShopSQLConn);
            ShopStockAdapter = new SqlDataAdapter(Constant.STOCK_PRODUCT_TABLE_SELECT_COMMAND, ShopSQLConn);
            TransactionAdapter = new SqlDataAdapter(Constant.TRANSACTION_TABLE_SELECT_COMMAND, ShopSQLConn);
            CheckOutItemAdapter = new SqlDataAdapter(Constant.CHECKOUTITEM_TABLE_SELECT_COMMAND, ShopSQLConn);
            StockAdapter = new SqlDataAdapter(Constant.STOCK_TABLE_SELECT_COMMAND, ShopSQLConn);
            CashierRegisterAdapter = new SqlDataAdapter(Constant.ACTIVED_CASHIERREGISTER_SELECT_COMMAND, ShopSQLConn);
            PriceTagAdapter = new SqlDataAdapter(Constant.ACTIVED_PRICETAG_SELECT_COMMAND, ShopSQLConn);
        }

        private void InitializeShopAdapterCommands()
        {
            ShopCheckOutItemCommandBuilder = new SqlCommandBuilder(CheckOutItemAdapter); // update need builder
            ShopStockCommandBuilder = new SqlCommandBuilder(StockAdapter);
            ShopTransactionCommandBuilder = new SqlCommandBuilder(TransactionAdapter);
            CashierRegisterCommandBuilder = new SqlCommandBuilder(CashierRegisterAdapter);
            PriceTagCommandBuililder = new SqlCommandBuilder(PriceTagAdapter);
        }

        private void FillShopDataSet()
        {
            ShopStaffAdapter.Fill(ShopDsTableList, Constant.CASHIER_TABLE);
            StockDiscountAdapter.Fill(ShopDsTableList, Constant.STOCK_PRODUCT_DISCOUNT_TABLE);
            ShopStockAdapter.Fill(ShopDsTableList, Constant.STOCK_PRODUCT_TABLE);
            TransactionAdapter.Fill(ShopDsTableList, Constant.TRANSACTION_TABLE);
            CheckOutItemAdapter.Fill(ShopDsTableList, Constant.CHECKOUTITEM_TABLE);
            StockAdapter.Fill(ShopDsTableList, Constant.STOCK_TABLE);
            CashierRegisterAdapter.Fill(ShopDsTableList, Constant.CASHIERREGISTER_TABLE);
            PriceTagAdapter.Fill(ShopDsTableList, Constant.PRICETAG_TABLE);
        }

        private void CheckAndCloseShopSQLConnection()
        {
            if (ShopSQLConn.State == ConnectionState.Open)
            {
                ShopSQLConn.Close();
            }
        }

        public DataTable getTable(string tableName)
        {
            return ShopDsTableList.Tables[tableName];
        }

        #endregion

        public void AddTransactionDataRow(string TransactionID, string CashierID, string MachineID, DateTime TransactionDate) 
        {
            DataTable table = ShopDsTableList.Tables[Constant.TRANSACTION_TABLE];
            table.Rows.Add(TransactionID, CashierID.PadLeft(Constant.FOUR, Constant.STRING_PAD), MachineID.PadLeft(Constant.FOUR, Constant.STRING_PAD), TransactionDate);
            ShopSQLConn.Open();
            TransactionAdapter.Update(ShopDsTableList, Constant.TRANSACTION_TABLE);
            ShopSQLConn.Close();
        }

        public void AddCheckOutItemDataRow(string TransactionID, string ProductID, DateTime BatchID, double CostPrice, double SellingPrice, double Discount, int Quantity)
        {
            DataTable table = ShopDsTableList.Tables[Constant.CHECKOUTITEM_TABLE];
            DataTable stock_table = ShopDsTableList.Tables[Constant.STOCK_TABLE];
            table.Rows.Add(TransactionID, ProductID, BatchID, CostPrice, SellingPrice, Discount, Quantity);
            string query = "ProductID = '" + ProductID + "'" + Constant.AND + "BatchID = '" + BatchID + "'";
            DataRow[] stockrows = stock_table.Select(query);
            int currentStock = int.Parse(stockrows[0][Constant.QUANTITY].ToString());
            stockrows[0][Constant.QUANTITY] = currentStock - Quantity;  // update the new stock quantity after purchased

            ShopSQLConn.Open();
            CheckOutItemAdapter.Update(ShopDsTableList, Constant.CHECKOUTITEM_TABLE);
            StockAdapter.Update(ShopDsTableList, Constant.STOCK_TABLE);
            ShopSQLConn.Close();
        }

        public void ReloadDataSet()
        {
            ShopSQLConn.Open();
            ShopDsTableList.Clear();
            FillShopDataSet();
            LoadActivedPriceTagAndProductID();
            ShopSQLConn.Close();
        }

        public List<string> GetActivedCashierRegisterIDs(out Dictionary<string, int> cashierRegisterInfo)
        {
            DataTable activedCashierRegister = ShopDsTableList.Tables[Constant.CASHIERREGISTER_TABLE];
            cashierRegisterInfo = new Dictionary<string, int>();
            List<string> cashierRegisterIds = new List<string>();
            foreach (DataRow dr in activedCashierRegister.Rows)
            {
                cashierRegisterIds.Add(dr[Constant.CASHIERREGISTER_ID].ToString());
                cashierRegisterInfo.Add(dr[Constant.CASHIERREGISTER_ID].ToString(), 0);
            }
            return cashierRegisterIds;
        }

        public List<string> GetActivedPriceTagIDs(out Dictionary<string, int> priceTagInfo)
        {
            DataTable activedPriceTag = ShopDsTableList.Tables[Constant.PRICETAG_TABLE];
            List<string> priceTagIds = new List<string>();
            priceTagInfo = new Dictionary<string, int>();

            foreach (DataRow dr in activedPriceTag.Rows)
            {
                priceTagIds.Add(dr[Constant.PRICETAG_ID].ToString());
                priceTagInfo.Add(dr[Constant.PRICETAG_ID].ToString(), 0);
            }
            return priceTagIds;
        }

        public string GetActivedPriceTagProductID(string machineID)
        {
            string productID = string.Empty;
            mPriceTagInfo.TryGetValue(machineID, out productID);
            return productID;
        }

        public void DeactiveMachine(List<string> cashierRegister, List<string> priceTag)
        {
            DataTable CR = ShopDsTableList.Tables[Constant.CASHIERREGISTER_TABLE];
            DataTable PT = ShopDsTableList.Tables[Constant.PRICETAG_TABLE];
            foreach (DataRow dr in CR.Rows)
            {
                if(cashierRegister.IndexOf(dr[Constant.CASHIERREGISTER_ID].ToString()) != -1)
                    dr[Constant.MACHINE_STATUS] = Constant.MACHINE_DEACTIVIED;
            }

            foreach (DataRow dr in PT.Rows)
            {
                if (cashierRegister.IndexOf(dr[Constant.PRICETAG_ID].ToString()) != -1)
                    dr[Constant.MACHINE_STATUS] = Constant.MACHINE_DEACTIVIED;
            }
            ShopSQLConn.Open();
            CashierRegisterAdapter.Update(ShopDsTableList, Constant.CASHIERREGISTER_TABLE);
            PriceTagAdapter.Update(ShopDsTableList, Constant.PRICETAG_TABLE);
            ShopSQLConn.Close();
        }

        private void LoadActivedPriceTagAndProductID()
        {
            DataTable activedPriceTag = ShopDsTableList.Tables[Constant.PRICETAG_TABLE];
            Dictionary<string, string> mPriceTagInfo = new Dictionary<string, string>();
            foreach (DataRow dr in activedPriceTag.Rows)
            {
                mPriceTagInfo.Add(dr[Constant.CASHIERREGISTER_ID].ToString(), dr[Constant.PRICETAG_PRODUCTID].ToString());
            }
        }

        //public ArrayList GetActivedCashierRegisterIDs()
        //{
        //    DataTable activedCashierRegister = ShopDsTableList.Tables[Constant.CASHIERREGISTER_TABLE];
        //    ArrayList cashierRegisterIds = new ArrayList();
        //    foreach (DataRow dr in activedCashierRegister.Rows)
        //    {
        //        KeyValuePair<string, int> valPair = new KeyValuePair<string, int>(dr[Constant.CASHIERREGISTER_ID].ToString(), 0);
        //        cashierRegisterIds.Add(valPair);
        //    }
        //    return cashierRegisterIds;
        //}

        //public ArrayList GetActivedPriceTagIDs()
        //{
        //    DataTable activedPriceTag = ShopDsTableList.Tables[Constant.PRICETAG_TABLE];
        //    ArrayList priceTagInfo = new ArrayList();
        //    foreach (DataRow dr in activedPriceTag.Rows)
        //    {
        //        KeyValuePair<string, int> valPair = new KeyValuePair<string, int>(dr[Constant.PRICETAG_ID].ToString(), 0);
        //        priceTagInfo.Add(dr[Constant.PRICETAG_ID].ToString());
        //    }
        //    return priceTagInfo;
        //}

        //public Dictionary<string, int> GetActivedCashierRegisters()
        //{
        //    DataTable activedCashierRegister = ShopDsTableList.Tables[Constant.CASHIERREGISTER_TABLE];
        //    Dictionary<string, int> cashierRegisterInfo = new Dictionary<string, int>();
        //    foreach (DataRow dr in activedCashierRegister.Rows)
        //    {
        //        cashierRegisterInfo.Add(dr[Constant.CASHIERREGISTER_ID].ToString(), 0);
        //    }
        //    return cashierRegisterInfo;
        //}

        //public Dictionary<string, int> GetActivedPriceTags()
        //{
        //    DataTable activedPriceTag = ShopDsTableList.Tables[Constant.PRICETAG_TABLE];
        //    Dictionary<string, int> priceTagInfo = new Dictionary<string, int>();
        //    foreach (DataRow dr in activedPriceTag.Rows)
        //    {
        //        priceTagInfo.Add(dr[Constant.PRICETAG_ID].ToString(), 0);
        //    }
        //    return priceTagInfo;
        //}

        //public void ReloadCashierRegister()
        //{
        //    ShopDsTableList.Tables[Constant.CASHIERREGISTER_TABLE].Clear();
        //    CashierRegisterAdapter.Fill(ShopDsTableList, Constant.CASHIERREGISTER_TABLE);
        //}

        //public void ReloadPriceTag()
        //{
        //    ShopDsTableList.Tables[Constant.PRICETAG_TABLE].Clear();
        //    PriceTagAdapter.Fill(ShopDsTableList, Constant.PRICETAG_TABLE);
        //    LoadActivedPriceTagAndProductID();
        //}
    }
}
